package actions;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import javax.naming.NamingException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import db.DbConnectionPool;
import db.DbException;

public class distributeAction extends Action {
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws DbException {
		try {
			Connection conn = DbConnectionPool.getConnection();

			Statement stmt = conn.createStatement();
			PreparedStatement pstmt = null;
			ResultSet rs = stmt
					.executeQuery("SELECT user_ref,user_name FROM user_roles WHERE role='reviewer'");
			
			Vector user_ref = new Vector();
			Vector user_name = new Vector();
			if (rs != null)
				while (rs.next()) {
					user_ref.add(rs.getInt(1));
					user_name.add(rs.getString(2));
				}
			rs = stmt
					.executeQuery("SELECT user_ref FROM user_roles WHERE role='student'");
		
			Vector student_ref = new Vector();
			Vector student_name = new Vector();
			if (rs != null)
				while (rs.next()) {
					Statement stmt2 = conn.createStatement();
					ResultSet rs2 = stmt2
							.executeQuery("SELECT student_id FROM user_link WHERE user_id='"
									+ rs.getInt(1) + "'");
					rs2.next();
					String studentId = Integer.toString(rs2.getInt(1));
					student_ref.add(studentId);
					Statement stmt3 = conn.createStatement();
					ResultSet stName = stmt3
							.executeQuery("SELECT name FROM student WHERE id='"
									+ studentId + "'");
					stName.next();
					student_name.add(stName.getString(1));
				}
			int length = user_ref.size();
			for (int i = 0; i < student_ref.size(); i++) {
				int pos = ((i+1) % length) - 1;
				if (pos < 0)
					pos = length - 1;
				pstmt = conn
						.prepareStatement("INSERT INTO list_application(student_id,student_name,reviewer_id,reviewer_name,graded)"
								+ "VALUES (?,?,?,?,?)");
				pstmt.setInt(1, Integer.parseInt(student_ref.get(i).toString()));
				pstmt.setString(2, student_name.get(i).toString());
				pstmt.setInt(3, Integer.parseInt(user_ref.get(pos).toString()));
				pstmt.setString(4, user_name.get(pos).toString());
				pstmt.setBoolean(5, false);
				pstmt.executeUpdate();
				pstmt = conn.prepareStatement("UPDATE reviewstart SET review='true'");
				pstmt.executeUpdate();
			}

			// pstmt.close();
			conn.commit();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		return mapping.findForward("success");
	}
}
